﻿Imports System.Data
Imports System.Data.SqlClient
Public Class OrdemServicoDAL

    Public Function RetItens(Codigo As Integer) As DataTable
        Dim tab As DataTable
        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "select *from vw_venItensOrdem where ord_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
            Return tab
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function RetItem(Codigo As Integer, ProCodigo As Integer) As DataTable
        Dim tab As DataTable
        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "select *from vw_venItensOrdem where ord_codigo = @codigo and pro_codigo=@procodigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            cmd.Parameters.Add(New SqlParameter("@procodigo", SqlDbType.Int)).Value = ProCodigo
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
            Return tab
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function IAEOrdem(Codigo As Integer, Total As Nullable(Of Decimal), DataInicial As Nullable(Of DateTime), DataFinal As Nullable(Of DateTime), CodFuncionario As Integer, CodPlaca As Integer, CodStatus As Integer, Desconto As Nullable(Of Decimal), Operacao As Char) As Integer
        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "execute SPOrdemServico @desconto,@total, @datainicial,@datafinal,@codfuncionario,@codplaca,@codstatus,@ordcodigo,@operacao "

            If (DataFinal.HasValue) Then : cmd.Parameters.Add(New SqlParameter("@datafinal", SqlDbType.DateTime)).Value = DataFinal
            Else : cmd.Parameters.Add(New SqlParameter("@datafinal", SqlDbType.DateTime)).Value = DBNull.Value
            End If

            If (DataInicial.HasValue) Then : cmd.Parameters.Add(New SqlParameter("@datainicial", SqlDbType.DateTime)).Value = DataInicial
            Else : cmd.Parameters.Add(New SqlParameter("@datainicial", SqlDbType.DateTime)).Value = DBNull.Value
            End If

            If (Desconto.HasValue) Then : cmd.Parameters.Add(New SqlParameter("@desconto", SqlDbType.Decimal)).Value = Desconto
            Else : cmd.Parameters.Add(New SqlParameter("@desconto", SqlDbType.Decimal)).Value = DBNull.Value
            End If

            If (Total.HasValue) Then : cmd.Parameters.Add(New SqlParameter("@total", SqlDbType.Decimal)).Value = Total
            Else : cmd.Parameters.Add(New SqlParameter("@total", SqlDbType.Decimal)).Value = DBNull.Value
            End If

            cmd.Parameters.Add(New SqlParameter("@codfuncionario", SqlDbType.Int)).Value = CodFuncionario
            cmd.Parameters.Add(New SqlParameter("@codplaca", SqlDbType.Int)).Value = CodPlaca
            cmd.Parameters.Add(New SqlParameter("@codstatus", SqlDbType.Int)).Value = CodStatus
            cmd.Parameters.Add(New SqlParameter("@ordcodigo", SqlDbType.Int)).Value = Codigo
            cmd.Parameters.Add(New SqlParameter("@operacao", SqlDbType.Char)).Value = Operacao
            Codigo = objconexao.executacmdAI(cmd, "Ofi_OrdemServico")
            Return Codigo
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function InsereAgendamento(Codigo As Integer) As Integer
        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "exec SPAgenOrdem @codigo "
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            Codigo = objconexao.executacmdAI(cmd, "Ofi_OrdemServico")
            Return Codigo
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Sub IAEItens(OrdCodigo As Integer, ProCodigo As Integer, ItoQtde As Integer, ItoValorUnitario As Decimal, ItoDesconto As Decimal, Total As Decimal, StaCodigo As Integer, Operacao As Char)
        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "execute SPOrdemServicoEstoque @ordcodigo,@procodigo, @itoqtde,@itovalorunitario,@itodesconto,@total,@stacodigo,@operacao "
            cmd.Parameters.Add(New SqlParameter("@ordcodigo", SqlDbType.Int)).Value = OrdCodigo
            cmd.Parameters.Add(New SqlParameter("@procodigo", SqlDbType.Int)).Value = ProCodigo
            cmd.Parameters.Add(New SqlParameter("@itoqtde", SqlDbType.Int)).Value = ItoQtde
            cmd.Parameters.Add(New SqlParameter("@itovalorunitario", SqlDbType.Decimal)).Value = ItoValorUnitario
            cmd.Parameters.Add(New SqlParameter("@total", SqlDbType.Decimal)).Value = Total
            cmd.Parameters.Add(New SqlParameter("@itodesconto", SqlDbType.Decimal)).Value = ItoDesconto
            cmd.Parameters.Add(New SqlParameter("@stacodigo", SqlDbType.Int)).Value = StaCodigo
            cmd.Parameters.Add(New SqlParameter("@operacao", SqlDbType.Char)).Value = Operacao
            objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

#Region "Buscar"
    Public Function buscar(ByVal Descricao As String, tipo As Integer) As DataTable
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0
                    cmd.CommandText = "select * from vw_VenOrdemServico where ord_codigo = @codigo and sta_codigo <> 9"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(Descricao)
                Case 1
                    cmd.CommandText = "select * from vw_VenOrdemServico where cli_nome COLLATE SQL_Latin1_General_CP1_CI_AI  like @descricao  and sta_codigo <> 9"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 2
                    cmd.CommandText = "select * from vw_VenOrdemServico where pla_descricao COLLATE SQL_Latin1_General_CP1_CI_AI  like @descricao  and sta_codigo <> 9"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 4
                    If (Descricao = "Aberto") Then
                        cmd.CommandText = "select * from vw_VenOrdemServico where sta_codigo <>5 and sta_codigo <> 9"
                    Else
                        cmd.CommandText = "select * from vw_VenOrdemServico where sta_codigo=5"
                    End If
                Case 5
                    cmd.CommandText = "select * from vw_VenOrdemServico where sta_codigo <> 9 order by sta_codigo, ord_codigo"
                Case Else
                    cmd.CommandText = "select * from vw_VenOrdemServico where sta_codigo <> 5 and sta_codigo <> 9 order by ord_codigo"
            End Select
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function

    Function locadata(ByVal dtini As Date, ByVal dtfim As Date) As DataTable 'Filtro datas
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand  'Format(Descricao, "yyyy/MM/dd")
            cmd.CommandText = "select * from vw_VenOrdemServico where ord_datainicial BETWEEN @dtini AND @dtfim  and sta_codigo <> 9"
            cmd.Parameters.Add(New SqlParameter("@dtini", SqlDbType.DateTime)).Value = Format(dtini, "yyyy/MM/dd 00:00:00")
            cmd.Parameters.Add(New SqlParameter("@dtfim", SqlDbType.DateTime)).Value = Format(dtfim, "yyyy/MM/dd 23:59:59")
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
End Class
